package com.wulian.common.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 验证Excel
 * 
 * @author gaoxiaolei
 *
 */
public class ExcelValidateUtil {

	 Logger logger = LoggerFactory.getLogger(getClass());
	/**
	 * @author gaoxiaolei
	 * 此方法验证模板文件中的内容与目标文件是否一致
	 * XXX 模板文件中所有的单元格的值必须是字符串类型. 并且不支持验证单元格合并、注释、公式的比较
	 * @param templateSheet
	 * @param newSheet
	 * @return
	 * @throws Exception 
	 */
	public boolean validate(HSSFSheet templateSheet, HSSFSheet newSheet) throws Exception {
		int firstRowNum = templateSheet.getFirstRowNum();
		int lastRowNum = templateSheet.getLastRowNum();
		for (int i = firstRowNum; i <= lastRowNum; i++) {
			HSSFRow templateSheetRow = templateSheet.getRow(i);
			HSSFRow newSheetRow = newSheet.getRow(i);
			if (templateSheetRow != null) {
				int firstCellNum = templateSheetRow.getFirstCellNum();
				int lastCellNum = templateSheetRow.getLastCellNum();
				for (int j = firstCellNum; j <= lastCellNum; j++) {
					HSSFCell templateSheetRowCell = templateSheetRow.getCell(j);
					HSSFCell newSheetRowCell = newSheetRow.getCell(j);
					if (templateSheetRowCell != null&&templateSheetRowCell.getStringCellValue()!=null&&!templateSheetRowCell.getStringCellValue().equals("")) {
						// 如果导入的sheet中的单元格是空
						if (newSheetRowCell == null) {
							logger.info("新的SHEET中单元格第"+templateSheetRowCell.getColumnIndex()+"行");
							throw new Exception("cell is null");
							// 判断所在的Sheet名称是否相同
						} else if (!templateSheetRowCell.getSheet().getSheetName()
								.equals(newSheetRowCell.getSheet().getSheetName())) {
							logger.info("所在Sheet的名称不一致");
							throw new Exception("sheetName is different");
							// 判断单元格所在列
						} else if(templateSheetRowCell.getCellType()!=newSheetRowCell.getCellType()){
							logger.info("所在单元格类型不一致");
							throw new Exception("cell style is different");
						} else if (!templateSheetRowCell.getStringCellValue()
								.equals(newSheetRowCell.getStringCellValue())) {
							logger.info("单元格值不一致");
							throw new Exception("cellValue is different");
						}
					}
				}
			}
		}
		return true;
	}
	
	/**
	 * 验证类型是否匹配
	 * @param sheet sheet名称
	 * @param rowIndex 行索引,以0开始
	 * @param cellNumber列索引,以0开始
	 * @param cellType cell中值的类型
	 * @return
	 */
	public boolean validateCellType(HSSFSheet sheet,int rowIndex,int cellNumber,int cellType){
		HSSFRow row = sheet.getRow(rowIndex);
		if(row==null){
			System.out.println("此行为空");
			return false;
		}
		HSSFCell cell = row.getCell(cellNumber);
		if(cell==null){
			System.out.println("此cell为空");
			return false;
		}else if(cell.getCellType()!=cellType){
			System.out.println("此cell中的值类型与传入的类型不一致");
			return false;
		}
		return true;
	}
	
	/**
	 * 设置样式为字符类型
	 * @param wb
	 * @return
	 */
	public CellStyle getStringStyle(Workbook wb){
		CellStyle css = wb.createCellStyle();  
        DataFormat format = wb.createDataFormat();  
        css.setDataFormat(format.getFormat("@"));
        return css;
	}
	
	/**
	 * 设置样式为时间类型(yyyy/mm/dd)
	 */
	public CellStyle getDateStyle(Workbook wb){
		CellStyle css = wb.createCellStyle();  
        DataFormat format = wb.createDataFormat();  
        css.setDataFormat(format.getFormat("yyyy/mm/dd"));
        return css;
	}
}